-- Exercice 1

-- 1
SELECT Nom
FROM Departements
WHERE Lieu='Reims'

-- 2
SELECT Nom
FROM Employes
WHERE Fonction='Responsable'
	AND Salaire>=30000

-- 3
SELECT COUNT(*)
FROM Employes
WHERE DateEmbauche LIKE '2010%'

SELECT COUNT(*)
FROM Employes
WHERE YEAR(DateEmbauche)=2010

-- 4
SELECT Nom,COUNT(*)
FROM Departements
GROUP BY Nom

-- 5
SELECT Lieu,COUNT(*)
FROM Departements
GROUP BY Lieu

-- 6
SELECT Fonction,AVG(Salaire)
FROM Employes
GROUP BY Fonction


-- Exercice 2

-- 1
SELECT Nom
FROM Immeubles
WHERE NbEtages>10

-- 2
SELECT Nom
FROM Personnes
WHERE Age>=20 AND Age<=50

SELECT Nom
FROM Personnes
WHERE Age BETWEEN 20 AND 50

-- 3
SELECT Nom
FROM Personnes
WHERE Profession='Informaticien'
	AND Age>25

-- 4
SELECT *
FROM Appartements
WHERE Superficie>=200
	AND Etage<5

-- 5
SELECT COUNT(*)
FROM Personnes
WHERE Age>=50

-- 6
SELECT AVG(Superficie)
FROM Appartements

-- 7
SELECT AVG(Age)
FROM Personnes
WHERE Profession='Acteur'

-- 8
SELECT COUNT(*)
FROM Appartements
WHERE Etage=2

-- 9
SELECT AVG(Superficie)
FROM Appartements
WHERE Etage=2

-- 10
SELECT COUNT(*)
FROM Immeubles
WHERE NbEtages>5

-- 11
SELECT Etage,AVG(Superficie)
FROM Appartements
GROUP BY Etage

-- 12
SELECT Age,COUNT(*)
FROM Personnes
GROUP BY Age

-- 13
SELECT Superficie
FROM Appartements
GROUP BY Superficie
HAVING COUNT(*)>=2


-- Exercice 3

-- 1
SELECT *
FROM Produits
WHERE Poids<1000

-- 2
SELECT *
FROM Fournisseurs
WHERE Nom LIKE '%SARL%'

-- 3
SELECT Nom
FROM Couleurs
WHERE NOT (Nom='Noir' OR Nom='Blanc')

-- 4
SELECT COUNT(*)
FROM Villes

-- 5
SELECT COUNT(*)
FROM Usines
WHERE Nom LIKE '%SAS%'

-- 6
SELECT COUNT(*)
FROM Livraisons
WHERE Quantite>500

-- 7
SELECT COUNT(*)
FROM Livraisons
WHERE Date>='2016-03-01'
	AND Date<='2016-03-31'

SELECT COUNT(*)
FROM Livraisons
WHERE YEAR(Date)= 2016 AND MONTH(Date)=3

SELECT COUNT(*)
FROM Livraisons
WHERE Date LIKE '2016-03-%'

-- 8
SELECT Nom,COUNT(DISTINCT idCouleur)
FROM Produits
GROUP BY Nom